﻿Public Class XRAYITEM
    Dim connect As CONDB = CONDB.NewConnection()
    Private XRYID = Nothing
    Private CODE = Nothing
    Private PRDCODE = Nothing
    Private CXRYID = Nothing
    Private SXRYID = Nothing
    Private PRDCAT = Nothing
    Private PXRYID = Nothing
    Private ICD10TM = Nothing
    Private ICD9CM = Nothing
    Private FCODE = Nothing
    Private XRYENAME = Nothing
    Private XRYTNAME = Nothing
    Private CHKIN = Nothing
    Private REMARK = Nothing
    Private STTDATE As String = Nothing
    Private EXPDATE As String = Nothing
    Private COST = Nothing
    Private STATUS = Nothing
    Private GENERICNAME = Nothing
    Private CCODE = Nothing
    Private RCODE = Nothing
    Private OPDPRC = Nothing
    Private IPDPRC = Nothing
    Private F_STK = Nothing
    Private selectxrayitem = "SELECT `xryid` AS 'ID', xrayitem.`code` AS 'CODE', mpd.`prdcode` AS 'รหัสสินค้า', mc.`cxryname` AS 'ประเภท', mp.`sxryname` AS 'ส่วน', pd.`catname` AS 'ประเภทสินค้า', mpo.`pxryname` AS 'กลุ่ม', `icd10tm` AS 'รหัสหัตถการ ICD10-TM', `icd9cm` AS 'รหัสหัตถการ ICD9-CM', `fcode` AS 'รหัสบัญชีกลาง' , `xryename` AS 'ชื่อหัตถการภาษาอังกฤษ', `xrytname` AS 'ชื่อหัตถการภาษาไทย', `chkin` AS 'CHKIN', `remark` AS 'หมายเหตุ', `sttdate` AS 'วันที่เริ่มใช้', `expdate` AS 'วันที่เลิกใช้', `cost` AS 'ต้นทุน', mc.`status` AS 'สถานะการเปิดใช้งาน', mc.`cxryid` AS 'CXRYID', mp.`sxryid` as 'SXRYID', mpo.`pxryid` AS 'PXRYID', mpd.`prdname` AS 'สินค้า', mpd.`ccode` AS 'CCODE', cc.`name` AS 'ชื่อCCODE', mpd.`rcode` AS 'RCODE', rc.`name` AS 'ชื่อRCODE', mpd.`opdprc` AS 'ราคาขาย OPD', mpd.`ipdprc` AS 'ราคาขาย ipd', mpd.`f_stk`AS 'ตัดสต๊อค', `chkin` AS 'CHECKIN' FROM xrayitem LEFT JOIN (SELECT * FROM  masclsxray )  AS mc ON xrayitem.cxryid = mc.cxryid LEFT JOIN (SELECT * FROM maspartxray ) AS mp ON xrayitem.sxryid = mp.sxryid LEFT JOIN (SELECT * FROM masposxray ) AS mpo ON xrayitem.pxryid = mpo.pxryid LEFT JOIN masprdcats AS pd ON xrayitem.prdcat = pd.prdcat LEFT JOIN masproduct AS mpd ON xrayitem.prdcode = mpd.prdcode JOIN finchcode AS cc ON mpd.ccode = cc.ccode JOIN finrecode AS rc ON mpd.rcode = rc.rcode"
    'Private selectxrayitem = "SELECT `XRYID` as 'ID' ,XRAYITEM.`CODE` as 'CODE', MPD.`PRDCODE` as 'รหัสสินค้า',MC.`CXRYNAME` as 'ประเภท', MP.`SXRYNAME` as 'ส่วน',PD.`CATNAME` as 'ประเภทสินค้า',MPO.`PXRYNAME` as 'กลุ่ม',`ICD10TM` as 'รหัสหัตถการ ICD10-TM',`ICD9CM` as 'รหัสหัตถการ ICD9-CM',`FCODE` as 'รหัส 5 หลัก' ,`XRYENAME` as 'ชื่อหัตถการภาษาอังกฤษ', `XRYTNAME` as 'ชื่อหัตถการภาษาไทย',`CHKIN`,`REMARK` as 'หมายเหตุ',`STTDATE` as 'วันที่เริ่มใช้' ,`EXPDATE` as 'วันที่เลิกใช้' , `COST` as 'ต้นทุน',MC.`STATUS` as 'สถานะการเปิดใช้งาน',MC.`CXRYID`,MP.`SXRYID`,MPO.`PXRYID`,MPD.`PRDNAME` as 'สินค้า',MPD.`CCODE` as 'CCODE',MPD.`RCODE` as 'RCODE',MPD.`OPDPRC` as 'ราคาขาย OPD',MPD.`IPDPRC` as 'ราคาขาย IPD',MPD.`F_STK`as 'ตัดสต๊อค',`CHKIN` as 'CHECKIN'  FROM XRAYITEM LEFT JOIN (SELECT * FROM  MASCLSXRAY )  as MC ON XRAYITEM.CXRYID = MC.CXRYID LEFT JOIN (SELECT * FROM MASPARTXRAY ) as MP ON XRAYITEM.SXRYID = MP.SXRYID LEFT JOIN (SELECT * FROM MASPOSXRAY ) as MPO ON XRAYITEM.PXRYID = MPO.PXRYID LEFT JOIN MASPRDCATS as PD ON XRAYITEM.PRDCAT = PD.PRDCAT LEFT JOIN MASPRODUCT as MPD ON XRAYITEM.PRDCODE = MPD.PRDCODE"

    Property IPDPRC_() As Integer
        Get
            Return IPDPRC
        End Get
        Set(value As Integer)
            IPDPRC = value
        End Set
    End Property

    Property OPDPRC_() As Integer
        Get
            Return OPDPRC
        End Get
        Set(value As Integer)
            OPDPRC = value
        End Set
    End Property

    Property RCODE_() As Integer
        Get
            Return RCODE
        End Get
        Set(value As Integer)
            RCODE = value
        End Set
    End Property

    Property CCODE_() As Integer
        Get
            Return CCODE
        End Get
        Set(value As Integer)
            CCODE = value
        End Set
    End Property

    Property GENERICNAME_() As String
        Get
            Return GENERICNAME
        End Get
        Set(value As String)
            GENERICNAME = value
        End Set
    End Property

    Property selectxrayitem_() As String
        Get
            Return selectxrayitem
        End Get
        Set(value As String)

        End Set
    End Property

    Property XRYID_() As Integer
        Get
            Return XRYID
        End Get
        Set(value As Integer)
            XRYID = value
        End Set
    End Property

    Property CODE_() As String
        Get
            Return CODE
        End Get
        Set(value As String)
            CODE = value
        End Set
    End Property

    Property PRDCODE_() As Integer
        Get
            Return PRDCODE
        End Get
        Set(value As Integer)
            PRDCODE = value
        End Set
    End Property

    Property CXRYID_() As Integer
        Get
            Return CXRYID
        End Get
        Set(value As Integer)
            CXRYID = value
        End Set
    End Property

    Property SXRYID_() As Integer
        Get
            Return SXRYID
        End Get
        Set(value As Integer)
            SXRYID = value
        End Set
    End Property

    Property PRDCAT_() As Integer
        Get
            Return PRDCAT
        End Get
        Set(value As Integer)
            PRDCAT = value
        End Set
    End Property

    Property PXRYID_() As Integer
        Get
            Return PXRYID
        End Get
        Set(value As Integer)
            PXRYID = value
        End Set
    End Property

    Property ICD10TM_() As String
        Get
            Return ICD10TM
        End Get
        Set(value As String)
            ICD10TM = value
        End Set
    End Property

    Property ICD9CM_() As String
        Get
            Return ICD9CM
        End Get
        Set(value As String)
            ICD9CM = value
        End Set
    End Property

    Property FCODE_() As String
        Get
            Return FCODE
        End Get
        Set(value As String)
            FCODE = value
        End Set
    End Property

    Property XRYENAME_() As String
        Get
            Return XRYENAME
        End Get
        Set(value As String)
            XRYENAME = value
        End Set
    End Property

    Property XRYTNAME_() As String
        Get
            Return XRYTNAME
        End Get
        Set(value As String)
            XRYTNAME = value
        End Set
    End Property

    Property CHKIN_() As Boolean
        Get
            Return CHKIN
        End Get
        Set(value As Boolean)
            If value = True Then
                CHKIN = 1
            Else
                CHKIN = 0
            End If
        End Set
    End Property

    Property REMARK_() As String
        Get
            Return REMARK
        End Get
        Set(value As String)
            REMARK = value
        End Set
    End Property

    Property STTDATE_() As String
        Get
            Return STTDATE
        End Get
        Set(value As String)
            STTDATE = value
        End Set
    End Property

    Property EXPDATE_() As String
        Get
            Return EXPDATE
        End Get
        Set(value As String)
            EXPDATE = value
        End Set
    End Property

    Property COST_() As String
        Get
            Return COST
        End Get
        Set(value As String)
            COST = value
        End Set
    End Property

    Property STATUS_() As Boolean
        Get
            Return STATUS
        End Get
        Set(value As Boolean)
            If value = True Then
                STATUS = 1
            Else
                STATUS = 0
            End If
        End Set
    End Property
    Property F_STK_() As Boolean
        Get
            Return F_STK
        End Get
        Set(value As Boolean)
            If value = True Then
                F_STK = 1
            Else
                F_STK = 0
            End If
        End Set
    End Property
    Public Sub loadcombobox(ByVal MaspartxrayCmb As ComboBox)
        Dim MASCLSXRAY As DataTable
        MASCLSXRAY = connect.GetTable("SELECT `cxryid`, `cxryname` FROM masclsxray WHERE `status` = 1 ")
        Try
            With MaspartxrayCmb
                .DataSource = MASCLSXRAY
                .DisplayMember = "CXRYNAME"
                .ValueMember = "CXRYID"
                '.BindingContext = BindingContext
            End With
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
    Public Sub loadcombobox1(ByVal Maspartxraycmb As ComboBox)
        Dim MASPARTXRAY As DataTable
        MASPARTXRAY = connect.GetTable("SELECT `sxryid`, `sxryname` FROM maspartxray WHERE `status` = 1 ")
        Try
            With Maspartxraycmb
                .DataSource = MASPARTXRAY
                .DisplayMember = "SXRYNAME"
                .ValueMember = "SXRYID"
                '.BindingContext = BindingContext
            End With
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
    Public Sub loadcombobox2(ByVal Masposxraycmb As ComboBox)
        Dim MASPOSXRAY As DataTable
        MASPOSXRAY = connect.GetTable("SELECT `pxryid`, `pxryname` FROM masposxray WHERE `status` = 1 ")
        Try
            With Masposxraycmb
                .DataSource = MASPOSXRAY
                .DisplayMember = "PXRYNAME"
                .ValueMember = "PXRYID"
                '.BindingContext = BindingContext
            End With
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
    Public Sub loadcombobox3(ByVal CCODECmb As ComboBox)
        Dim FINCHCODE As DataTable
        FINCHCODE = connect.GetTable("SELECT `ccode`, `name` FROM finchcode WHERE `status` = 1 ")
        Try
            With CCODECmb
                .DataSource = FINCHCODE
                .DisplayMember = "NAME"
                .ValueMember = "CCODE"
                '.BindingContext = BindingContext
            End With
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
    Public Sub loadcombobox4(ByVal RCODECmb As ComboBox)
        Dim FINRECODE As DataTable
        FINRECODE = connect.GetTable("SELECT `rcode`, `name` FROM finrecode WHERE `status` = 1 ")
        Try
            With RCODECmb
                .DataSource = FINRECODE
                .DisplayMember = "NAME"
                .ValueMember = "RCODE"
                '.BindingContext = BindingContext
            End With
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
    Public Sub insertXrayitem()
        If TypeOf CODE Is String And TypeOf XRYENAME Is String And TypeOf XRYTNAME Is String Then
            If connect.GetTable("SELECT `xryid` FROM xrayitem WHERE  `xryename` = '" & XRYENAME & "' AND `xrytname` = '" & XRYTNAME & "' AND `prdcode` = '" & PRDCODE & "'").Rows.Count > 0 Then
                MsgBox("มีข้อมูลนี้อยู่ในระบบแล้ว", MsgBoxStyle.Critical)
            Else
                connect = CONDB.NewConnection()
                connect.BeginTrans()
                Try
                    Dim productCode As Integer
                    productCode = connect.ExecuteScalar("INSERT INTO masproduct (`prdcat`, `prdname`, `ccode`, `rcode`,`opdprc`,`ipdprc`,`status`,`f_stk`) VALUES ('4', '" & XRYENAME & "', '" & CCODE & "', '" & RCODE & "','" & OPDPRC & "','" & IPDPRC & "', '" & STATUS & "','" & F_STK & "'); SELECT LAST_INSERT_ID();")
                    Dim sql As String
                    sql = "INSERT INTO xrayitem"
                    sql += "(`code`,`prdcode`,`cxryid`,`sxryid`,`pxryid`,`icd10tm`,`icd9cm`,`fcode`,`xryename`,`xrytname`,`remark`,`sttdate`,`expdate`,`cost`,`chkin`,`status`)"
                    sql += "VALUES ("
                    sql += "'" & CODE & "',"
                    sql += "'" & productCode & "',"
                    sql += "'" & CXRYID & "',"
                    sql += "'" & SXRYID & "',"
                    sql += "'" & PXRYID & "',"
                    sql += "'" & ICD10TM & "',"
                    sql += "'" & ICD9CM & "',"
                    sql += "'" & FCODE & "',"
                    sql += "'" & XRYENAME & "',"
                    sql += "'" & XRYTNAME & "',"
                    sql += "'" & REMARK & "',"
                    sql += "'" & STTDATE & "',"
                    sql += "'" & EXPDATE & "',"
                    sql += "'" & COST & "',"
                    sql += "'" & CHKIN & "',"
                    sql += "'" & STATUS & "')"
                    connect.ExecuteNonQuery(sql)
                    connect.CommitTrans()
                    MsgBox("ท่านได้ทำการเพิ่มข้อมูลเรียบร้อยแล้ว", MsgBoxStyle.Information)
                Catch ex As Exception
                    connect.RollbackTrans()
                    MsgBox(ex.ToString)
                Finally
                    connect.Dispose()
                End Try
            End If
        Else
            MsgBox("โปรดกรุณาระบุข้อมูลให้ถูกต้อง ต้องเป็นตัวอักษร", MsgBoxStyle.Critical)
        End If
    End Sub
    Public Sub editXrayitem()
        If TypeOf CODE Is String And TypeOf XRYENAME Is String And TypeOf XRYTNAME Is String Then
            connect = CONDB.NewConnection()
            connect.BeginTrans()
            Try
                Dim sql As String
                sql = "UPDATE xrayitem SET"
                sql += "`code` = '" & CODE & "',"
                sql += "`cxryid` = '" & CXRYID & "',"
                sql += "`sxryid` = '" & SXRYID & "',"
                If PXRYID = 0 Then
                    sql += "`pxryid` = Null ,"
                Else
                    sql += "`pxryid` = '" & PXRYID & "',"
                End If
                sql += "`icd10tm` = '" & ICD10TM & "',"
                sql += "`icd9cm` = '" & ICD9CM & "',"
                sql += "`fcode` = '" & FCODE & "',"
                sql += "`xryename` = '" & XRYENAME & "',"
                sql += "`xrytname` = '" & XRYTNAME & "',"
                sql += "`remark` = '" & REMARK & "',"
                If STTDATE Is Nothing Then
                    sql += "`sttdate` = Null,"
                Else
                    sql += "`sttdate` = '" & STTDATE & "',"
                End If
                If EXPDATE Is Nothing Then
                    sql += "`expdate` = Null,"
                Else
                    sql += "`expdate` = '" & EXPDATE & "',"
                End If
                sql += "`cost` = '" & COST & "',"
                sql += "`chkin` = '" & CHKIN & "',"
                sql += "`status` = '" & STATUS & "' "
                sql += "WHERE `xryid` = '" & XRYID & "';"
                sql += "UPDATE masproduct SET "
                sql += "`prdname` = '" & XRYENAME & "', "
                sql += "`ccode` = '" & CCODE & "', "
                sql += "`rcode` = '" & RCODE & "', "
                sql += "`opdprc` = '" & OPDPRC & "', "
                sql += "`ipdprc` = '" & IPDPRC & "', "
                sql += "`f_stk` = '" & F_STK & "', "
                sql += "`status` = '" & STATUS & "' "
                sql += "WHERE `prdcode` = '" & PRDCODE & "'"
                connect.ExecuteNonQuery(sql)
                connect.CommitTrans()
                MsgBox("ท่านได้ทำการแก้ไขข้อมูลเรียบร้อยแล้ว", MsgBoxStyle.Information)

            Catch ex As Exception
                connect.RollbackTrans()
                MsgBox(ex.ToString)
            Finally
                connect.Dispose()
            End Try
        Else
            MsgBox("โปรดกรุณาระบุข้อมูลให้ถูกต้อง ต้องเป็นตัวอักษร", MsgBoxStyle.Critical)
        End If
    End Sub

End Class
